import java.sql.*;
import java.util.ArrayList;
import javax.swing.JOptionPane;

public class JPhotoDB {
	private Connection conn;

	public static void main(String[] args) {
		JPhotoDB db = new JPhotoDB();
		db.createTables();
		db.addTag("sup", "path");
		db.close();
	}

	public JPhotoDB() {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://localhost/feedback";
			conn = DriverManager.getConnection(url, "sqluser", "sqluserpw");
		} catch (ClassNotFoundException ex) {
			System.err.println(ex.getMessage());
		} catch (IllegalAccessException ex) {
			System.err.println(ex.getMessage());
		} catch (InstantiationException ex) {
			System.err.println(ex.getMessage());
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	public void close() {
		try {
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void createTables() {
		try {
			Statement st = conn.createStatement();
			st.executeUpdate("CREATE TABLE IF NOT EXISTS tags(id INT AUTO_INCREMENT, name VARCHAR(30), path VARCHAR(100), PRIMARY KEY (id))");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	@SuppressWarnings("static-access")
	public void addTag(String tag, String path) {
		String query = "SELECT path FROM tags WHERE name = '" + tag + "'";
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				String p = rs.getString("path");
				if (p.equals(path)) {
					JOptionPane jpo = new JOptionPane();
					jpo.showMessageDialog(null, "Image already tagged!",
							"Tagging Error", JOptionPane.ERROR_MESSAGE);
					return;
				}
			}
			st.executeUpdate("INSERT INTO tags(name, path) VALUES('" + tag
					+ "','" + path + "')");
			System.out.println("inserted into tags");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	public ArrayList<String> showImgTags(String path) {
		ArrayList<String> res = new ArrayList<String>();
		String s;
		String query = "SELECT name FROM tags WHERE path = '" + path + "'";
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				s = rs.getString("name");
				res.add(s);
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return res;
	}

	public ArrayList<String> tagSearch(String tag) {
		ArrayList<String> res = new ArrayList<String>();
		String s;
		String query = "SELECT path FROM tags WHERE name = '" + tag + "'";
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				s = rs.getString("path");
				res.add(s);
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return res;
	}
}
